package com.chenyue.cm.match.dao.provider;

import org.apache.ibatis.jdbc.SQL;

/**
 * @author libingxing
 */
public class MatchLiveUserProvider {

    public String liveUserMatchHistoryList(Integer userId, String convertPage) {
        return new SQL() {
            {
                SELECT("m.live_record_id as recordId");
                SELECT("m.user_id as userId");
                SELECT("r.title as title");
                SELECT("r.live_logo as logo");
                SELECT("r.start_time as startTime");
                SELECT("r.end_time as endTime");
                FROM("jd_match_live_user m");
                LEFT_OUTER_JOIN("jd_live_record r on r.id = m.live_record_id");
                WHERE("m.user_id = #{userId}");
            }
        } + convertPage;
    }

    public String historyRecordRank(Integer id) {
        return new SQL() {
            {
                SELECT("f.rank as rank");
                FROM("jd_match_format_member f");
                WHERE("f.user_id = #{id}");
                ORDER_BY("f.rank desc");
            }
        } + " limit 1";
    }

    public String historyRecordScore(Integer id) {
        return new SQL() {
            {
                SELECT("max(f.score) as score");
                FROM("jd_match_format_member f");
                WHERE("f.user_id = #{id}");
            }
        } + "";
    }

    public String matchGroupScoreList(Integer recordId, Integer itemId, String page) {
        return new SQL() {
            {
                SELECT("ml.group_id as groupId");
                SELECT("g.name as groupName");
                SELECT("(select sum(score) from jd_match_score_record " +
                        "where group_id = g.id and " +
                        (itemId != null && itemId > 0 ? "item_id = #{itemId} and " : "") +
                        "format_id = r.arrange_id) " +
                        "as groupScore");
                FROM("jd_match_live_user ml");
                LEFT_OUTER_JOIN("jd_match_group g on ml.group_id = g.id");
                LEFT_OUTER_JOIN("jd_live_record r on r.id = #{recordId}");
                WHERE("ml.live_record_id = #{recordId}");
                GROUP_BY("ml.group_id");
                ORDER_BY("groupScore desc");
            }
        } + page;
    }

    public String matchGroupScoreListCount(Integer recordId, Integer itemId) {
       String support = new SQL() {
            {
                SELECT("1");
                FROM("jd_match_live_user ml");
                LEFT_OUTER_JOIN("jd_match_group g on ml.group_id = g.id");
                LEFT_OUTER_JOIN("jd_live_record r on r.id = #{recordId}");
                WHERE("ml.live_record_id = #{recordId}");
                GROUP_BY("ml.group_id");
            }
        } + "";
        return new SQL() {
            {
                SELECT("count(0) as count");
                FROM("( " + support + " )s");
            }
        } + "";
    }

    public String matchMemberScoreList(Integer recordId, Integer itemId, Integer groupId, String page) {
        return new SQL() {
            {
                SELECT("u.user_name as userName");
                SELECT("u.head_img as headImg");
                SELECT("(select sum(score) from jd_match_score_record where" +
                        " format_id = r.arrange_id and " +
                        (itemId != null && itemId > 0 ? "item_id = #{itemId} and " : "") +
                        "user_id = ml.user_id) as score");
                SELECT("u.id as userId");
                SELECT("r.arrange_id as formatId");
                SELECT("(select arrange_order from jd_match_format where id = r.arrange_id) as arrangeOrder");
                SELECT("(select promotion_state from jd_match_format_member where user_id = u.id and format_id = r.arrange_id) as promotionState");
                SELECT("(select is_concat from my_match where user_id = u.id and live_record_id = r.id) as is_concat");
                FROM("jd_match_live_user ml");
                LEFT_OUTER_JOIN("jd_live_record r on r.id = #{recordId}");
                LEFT_OUTER_JOIN("jd_app_user u on u.id = ml.user_id");
                WHERE("ml.user_id = u.id");
                WHERE("ml.live_record_id = #{recordId}");
                if (groupId != null) {
                    WHERE("ml.group_id = #{groupId}");
                }
                GROUP_BY("ml.user_id");
                ORDER_BY("score desc");
                ORDER_BY("is_concat desc");
            }
        } + page;
    }

    public String matchMemberScoreListCount(Integer recordId, Integer itemId, Integer groupId) {
        String support = new SQL() {
            {
                SELECT("1");
                FROM("jd_match_live_user ml");
                LEFT_OUTER_JOIN("jd_live_record r on r.id = #{recordId}");
                LEFT_OUTER_JOIN("jd_app_user u on u.id = ml.user_id");
                WHERE("ml.user_id = u.id");
                WHERE("ml.live_record_id = #{recordId}");
                if (groupId != null) {
                    WHERE("ml.group_id = #{groupId}");
                }
                GROUP_BY("ml.user_id");
            }
        } + "";
        return new SQL() {
            {
                SELECT("count(0) as count");
                FROM("( " + support + " )s");
            }
        } + "";
    }

    public String handlePromotionGroupList(String formatId, Integer itemId) {
        return new SQL() {
            {
                SELECT("ml.group_id as groupId");
                SELECT("g.name as groupName");
                SELECT("(select sum(score) from jd_match_score_record " +
                        "where group_id = g.id and " +
                        (itemId != null && itemId > 0 ? "item_id = #{itemId} and " : "") +
                        "format_id = #{formatId}) " +
                        "as groupScore");
                FROM("jd_match_live_user ml");
                LEFT_OUTER_JOIN("jd_match_group g on ml.group_id = g.id");
                WHERE("ml.format_id = #{formatId}");
                GROUP_BY("ml.group_id");
                ORDER_BY("groupScore desc");
            }
        } + "";
    }

    public String handlePromotionScoreList(String formatId, Integer itemId, Integer groupId) {
        return new SQL() {
            {
                SELECT("u.user_name as userName");
                SELECT("u.head_img as headImg");
                SELECT("(select sum(score) from jd_match_score_record where" +
                        " format_id = #{formatId} and " +
                        (itemId != null && itemId > 0 ? "item_id = #{itemId} and " : "") +
                        "user_id = ml.user_id) as score");
                SELECT("u.id as userId");
                SELECT("ml.format_id as formatId");
                SELECT("(select arrange_order from jd_match_format where id = #{formatId}) as arrangeOrder");
                SELECT("(select promotion_state from jd_match_format_member where user_id = u.id and format_id = #{formatId}) as promotionState");
                FROM("jd_match_live_user ml");
                LEFT_OUTER_JOIN("jd_app_user u on u.id = ml.user_id");
                WHERE("ml.user_id = u.id");
                WHERE("ml.format_id = #{formatId}");
                if (groupId != null && groupId > 0) {
                    WHERE("ml.group_id = #{groupId}");
                }
                GROUP_BY("ml.user_id");
                ORDER_BY("score desc");
            }
        } + "";
    }

    public String singleCalculate(Integer formatId) {
        return new SQL() {
            {
                SELECT("(SELECT SUM(score) FROM jd_match_score_record WHERE format_id = #{formatId} and user_id = m.user_id) as score");
                SELECT("m.user_id as userId");
                SELECT("m.group_id as groupId");
                FROM("jd_match_live_user m");
                WHERE("m.format_id = #{formatId}");
            }
        } + "";
    }

    public String multiCalculate(Integer formatId) {
        String from = new SQL() {
            {
                SELECT("(SELECT SUM( score ) FROM jd_match_score_record WHERE user_id = r.user_id AND format_id = #{formatId} order by score limit 2 ) AS score");
                SELECT("r.user_id");
                FROM("jd_match_score_record r");
                WHERE("format_id = #{formatId}");
                GROUP_BY("r.user_id");
                ORDER_BY("score");
            }
        } + "";
        return new SQL() {
            {
                SELECT("(SELECT SUM(score) AS score FROM (" + from + ")t where t.user_id = m.user_id) as score");
                SELECT("m.user_id as userId");
                SELECT("m.group_id as groupId");
                FROM("jd_match_live_user m");
                WHERE("m.format_id = #{formatId}");
                ORDER_BY("score desc");
            }
        } + "";
    }

    public String getMatchScoreList(Integer formatId, Integer page, Integer rows) {
        return new SQL() {
            {
                SELECT("l.user_id userId");
                SELECT("u.user_name userName");
                SELECT("l.match_id matchId");
                SELECT("l.format_id formatId");
                SELECT("l.group_id groupId");
                SELECT("(SELECT g.`name` FROM jd_match_group as g WHERE g.match_id = r.match_id and g.id = l.group_id) as `groupName`");
                SELECT("r.item_id as `itemIds`");
                SELECT("(SELECT i.`name` FROM jd_match_item as i WHERE i.id = r.item_id) as `itemNames`");
                SELECT("(SELECT sr.score FROM jd_match_score_record AS sr WHERE sr.format_id = l.format_id  AND sr.user_id = l.user_id  AND sr.item_id = r.item_id AND sr.group_id = l.group_id ) AS score");
                SELECT("f.update_type updateType");
                SELECT("(select promotion_state from jd_match_format_member where user_id = u.id and format_id = l.format_id) as isUpdate");
                SELECT("f.arrange_order arrangeOrder");
                FROM("jd_match_live_user AS l");
                LEFT_OUTER_JOIN("jd_match_item_relation AS r ON l.match_id = r.match_id ");
                LEFT_OUTER_JOIN("jd_app_user u on u.id = l.user_id");
                LEFT_OUTER_JOIN("jd_match_format as f on f.id = l.format_id ");
                WHERE("l.format_id = #{formatId}");
                ORDER_BY("l.group_id");
            }
        } + " limit " +
                (page - 1) * rows + "," + rows;
    }

    public String getMatchScoreListCount(Integer formatId) {
        return new SQL() {
            {
                SELECT("count(1)");
                FROM("jd_match_live_user AS l");
                LEFT_OUTER_JOIN("jd_match_item_relation AS r ON l.match_id = r.match_id ");
                LEFT_OUTER_JOIN("jd_app_user u on u.id = l.user_id");
                LEFT_OUTER_JOIN("jd_match_format as f on f.id = l.format_id ");
                WHERE("l.format_id = #{formatId}");
                ORDER_BY("l.group_id");
            }
        } + "";
    }

}